﻿using System;
using System.Collections.Generic;
using System.Text;

namespace SharpSoft.Data.GSQL
{
    /// <summary>
    /// 原生SQL语句构建器
    /// </summary>
    public class SQLBuilder
    {
        internal SQLBuilder(SQLTextGenerator textGenerator)
        {
            this.sqlTextGenerator = textGenerator;
        }
        private readonly SQLTextGenerator sqlTextGenerator;
        /// <summary>
        /// 获取一个查询条件构建器
        /// </summary>
        /// <returns></returns>
        public QueryConditionBuilder GetQueryConditionBuilder()
        {
            var qcb = new QueryConditionBuilder(this.sqlTextGenerator);
            return qcb;
        }

        /// <summary>
        /// 获取LAST_INSERT_ID的查询语句
        /// </summary>
        /// <returns></returns>
        public string SelectLastId()
        {
            if (sqlTextGenerator is MySqlTextGenerator)
            {
                return "SELECT LAST_INSERT_ID();";
            }
            else if (sqlTextGenerator is SqliteTextGenerator)
            {
                return "SELECT LAST_INSERT_ROWID();";
            }
            else if (sqlTextGenerator is MsSqlTextGenerator)
            {
                return "SELECT @@IDENTITY;";
            }
            else
            {
                throw new NotSupportedException();
            }

        }
        /// <summary>
        /// 处理WHERE语句
        /// </summary>
        /// <param name="action"></param>
        /// <param name="sb"></param>
        private void ProcessWhereWithCondition(Action<QueryConditionBuilder> action, StringBuilder sb)
        {
            sb.Append(" WHERE ");
            if (action != null)
            {
                var qcb = new QueryConditionBuilder(this.sqlTextGenerator);
                action.Invoke(qcb);
                sb.Append(qcb.ToText());
            }
            else
            {
                sb.Append("(1 = 1)");
            }
        }
        /// <summary>
        /// 使用主键作为查询条件
        /// </summary>
        /// <param name="modelDescriptor"></param>
        /// <param name="sb"></param>
        private void ProcessWhereWithPrimaryKey(ModelDescriptor modelDescriptor, StringBuilder sb)
        {
            sb.Append(" WHERE ");
            var pk = modelDescriptor.GetPrimaryKey();
            if (pk.Length > 0)
            {
                var qcb = new QueryConditionBuilder(this.sqlTextGenerator);
                foreach (var item in pk)
                {
                    var vname = item.Property.Name;
                    if (!vname.StartsWith('@'))
                    {
                        vname = $"@{vname}";
                    }
                    qcb.and().equal(item.GetNameInDb(), vname);
                }
                sb.Append(qcb.ToText());
            }
            else
            {
                sb.Append("(1 = 1)");
            }
        }
        /// <summary>
        /// 创建插入语句
        /// </summary>
        /// <param name="table"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public string BuildInsert(string table, params string[] columns)
        {
            StringBuilder sb = new StringBuilder("INSERT INTO ");
            sb.Append(sqlTextGenerator.SafetiyName(table));
            sb.Append("(");
            List<string> safenames = new List<string>(columns.Length);
            List<string> values = new List<string>(columns.Length);
            foreach (var item in columns)
            {
                safenames.Add(sqlTextGenerator.SafetiyName(item));
                values.Add("@" + item);
            }
            sb.Append(string.Join(',', safenames));
            sb.Append(")VALUES(");
            sb.Append(string.Join(',', values));
            sb.Append(");");
            return sb.ToString();
        }
        /// <summary>
        /// 根据指定的模型描述创建插入指令
        /// </summary>
        /// <param name="modelDescriptor"></param>
        /// <returns></returns>
        public string BuildInsert(ModelDescriptor modelDescriptor)
        {
            StringBuilder sb = new StringBuilder("INSERT INTO ");
            sb.Append(sqlTextGenerator.SafetiyName(modelDescriptor.GetNameInDb()));
            sb.Append("(");
            var fds = modelDescriptor.GetFieldDescriptors();
            List<string> safenames = new List<string>(fds.Length);
            List<string> values = new List<string>(fds.Length);
            foreach (var item in fds)
            {
                if (item.IsVirtual || !item.ForInsert)
                {
                    continue;
                }
                safenames.Add(sqlTextGenerator.SafetiyName(item.GetNameInDb()));
                values.Add("@" + item.Property.Name);
            }
            if (safenames.Count == 0)
            {
                throw new Exception($"模型：[{modelDescriptor.ModelType.FullName}]未指定可用于插入的字段");
            }
            sb.Append(string.Join(',', safenames));
            sb.Append(")VALUES(");
            sb.Append(string.Join(',', values));
            sb.Append(");");
            return sb.ToString();
        }

        /// <summary>
        /// 构建UPDATE语句
        /// </summary>
        /// <param name="table"></param>
        /// <param name="columns"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        public string BuildUpdate(string table, string[] columns, Action<QueryConditionBuilder> action)
        {
            StringBuilder sb = new StringBuilder("UPDATE ");
            sb.Append(sqlTextGenerator.SafetiyName(table));
            sb.Append(" SET ");
            List<string> values = new List<string>(columns.Length);
            foreach (var item in columns)
            {
                values.Add(sqlTextGenerator.SafetiyName(item) + " = @" + item);
            }
            sb.Append(string.Join(',', values));

            ProcessWhereWithCondition(action, sb);

            sb.Append(";");
            return sb.ToString();
        }
        /// <summary>
        /// 根据指定的模型描述生成更新语句
        /// </summary>
        /// <param name="modelDescriptor"></param>
        /// <param name="action">为null表示使用模型指定主键作为查询条件</param>
        /// <returns></returns>
        public string BuildUpdate(ModelDescriptor modelDescriptor, Action<QueryConditionBuilder> action = null)
        {
            StringBuilder sb = new StringBuilder("UPDATE ");
            sb.Append(sqlTextGenerator.SafetiyName(modelDescriptor.GetNameInDb()));
            sb.Append(" SET ");
            var fds = modelDescriptor.GetFieldDescriptors();
            List<string> values = new List<string>(fds.Length);
            foreach (var item in fds)
            {
                if (item.IsVirtual || !item.ForUpdate)
                {
                    continue;
                }
                values.Add(sqlTextGenerator.SafetiyName(item.GetNameInDb()) + " = @" + item.Property.Name);
            }
            if (values.Count == 0)
            {
                throw new Exception($"模型：[{modelDescriptor.ModelType.FullName}]未指定可用于更新的字段");
            }
            sb.Append(string.Join(',', values));

            if (action == null)
            {
                ProcessWhereWithPrimaryKey(modelDescriptor, sb);
            }
            else
            {
                ProcessWhereWithCondition(action, sb);
            }

            sb.Append(";");
            return sb.ToString();
        }
        /// <summary>
        /// 构建查询语句
        /// </summary>
        /// <param name="table"></param>
        /// <param name="columns">为NULL表示查询所有列</param>
        /// <param name="qcbaction"></param>
        /// <returns></returns>
        public string BuildSelect(string table, string[] columns, Action<QueryConditionBuilder> qcbaction, Action<OrderBuilder> obaction = null, int? limitCount = null, int? limitOffset = null)
        {
            StringBuilder sb = new StringBuilder("SELECT ");
            if (columns == null)
            {
                sb.Append("*");
            }
            else
            {
                List<string> cols = new List<string>(columns.Length);
                foreach (var item in columns)
                {
                    cols.Add(sqlTextGenerator.SafetiyName(item));
                }
                sb.Append(string.Join(',', cols));
            }
            sb.Append(" FROM ");
            sb.Append(sqlTextGenerator.SafetiyName(table));

            ProcessWhereWithCondition(qcbaction, sb);

            if (obaction != null)
            {
                OrderBuilder ob = new OrderBuilder(this.sqlTextGenerator);
                obaction.Invoke(ob);
                if (!ob.IsEmpty)
                {
                    sb.Append(" ORDER BY ").Append(ob.ToString());
                }
            }
            if (limitCount.HasValue)
            {
                sb.Append($" LIMIT {limitOffset.GetValueOrDefault()},{limitCount.Value}");
            }
            else
            {
                if (limitOffset.HasValue)
                {
                    throw new Exception("单独指定Limit子句的LimitOffset项无效，必须指定LimitCount.");
                }
            }
            sb.Append(";");
            return sb.ToString();
        }
        /// <summary>
        /// 根据指定的模型描述生成查询语句
        /// </summary>
        /// <param name="modelDescriptor"></param>
        /// <param name="qcbaction"></param>
        /// <param name="obaction"></param>
        /// <returns></returns>
        public string BuildSelect(ModelDescriptor modelDescriptor, Action<QueryConditionBuilder> qcbaction, Action<OrderBuilder> obaction = null)
        {
            StringBuilder sb = new StringBuilder("SELECT ");
            var fds = modelDescriptor.GetFieldDescriptors();
            List<string> ql = new List<string>();
            foreach (var item in fds)
            {
                if (!string.IsNullOrWhiteSpace(item.CustomQueryCommandString))
                {//指定了自定义子查询
                    ql.Add($"({item.CustomQueryCommandString}) {sqlTextGenerator.SafetiyName(item.GetPropertyName())}");
                }
                else
                {
                    if (!item.ForSelect)
                    {
                        continue;
                    }
                    if (!string.IsNullOrEmpty(item.MappingName))
                    {//指定了映射名称,为查询结果指定别名
                        ql.Add($"{sqlTextGenerator.SafetiyName(item.MappingName)} {sqlTextGenerator.SafetiyName(item.GetPropertyName())}");
                    }
                    else
                    {//没有别名
                        ql.Add(sqlTextGenerator.SafetiyName(item.GetPropertyName()));
                    }
                }
            }
            sb.Append(string.Join(',', ql.ToArray()));

            sb.Append(" FROM ");
            sb.Append(sqlTextGenerator.SafetiyName(modelDescriptor.GetNameInDb()));

            ProcessWhereWithCondition(qcbaction, sb);

            if (obaction != null)
            {
                OrderBuilder ob = new OrderBuilder(this.sqlTextGenerator);
                obaction.Invoke(ob);
                if (!ob.IsEmpty)
                {
                    sb.Append(" ORDER BY ").Append(ob.ToString());
                }
            }

            sb.Append(";");
            return sb.ToString();
        }
        /// <summary>
        /// 构建查询数量的SQL语句
        /// </summary>
        /// <param name="table"></param>
        /// <param name="qcbaction"></param>
        /// <returns></returns>
        public string BuildSelectCount(string table, Action<QueryConditionBuilder> qcbaction = null)
        {
            StringBuilder sb = new StringBuilder("SELECT COUNT(1) FROM ");
            sb.Append(sqlTextGenerator.SafetiyName(table));

            ProcessWhereWithCondition(qcbaction, sb);


            sb.Append(";");
            return sb.ToString();
        }

        /// <summary>
        /// 构建删除语句
        /// </summary>
        /// <param name="table"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        public string BuildDelete(string table, Action<QueryConditionBuilder> action)
        {
            StringBuilder sb = new StringBuilder("DELETE FROM ");

            sb.Append(sqlTextGenerator.SafetiyName(table));

            ProcessWhereWithCondition(action, sb);

            sb.Append(";");
            return sb.ToString();
        }
        /// <summary>
        /// 构建删除语句
        /// </summary>
        /// <param name="modelDescriptor"></param>
        /// <param name="action">为null表示使用模型指定主键作为查询条件</param>
        /// <returns></returns>
        public string BuildDelete(ModelDescriptor modelDescriptor, Action<QueryConditionBuilder> action = null)
        {
            StringBuilder sb = new StringBuilder("DELETE FROM ");

            sb.Append(sqlTextGenerator.SafetiyName(modelDescriptor.GetNameInDb()));

            if (action == null)
            {
                ProcessWhereWithPrimaryKey(modelDescriptor, sb);
            }
            else
            {
                ProcessWhereWithCondition(action, sb);
            }

            sb.Append(";");
            return sb.ToString();
        }
    }
}
